<?php

/**
 * mysql数据库操作类
 * @author bing.peng
 *
 */
class DB {
	
	private $hostname 		= 'localhost';		// 数据库地址				
	private $username;							// 用户名
	private $password;							// 密码
	private $database;							// 数据库名
	private $charset 		= 'utf8';			// 数据库编码
	
	private $conn;								// 数据库连接标识
	private $result;							// 执行query命令的结果资源标识 
	
	private $insertId		= 0;  				// 执行查询后产生的id
	private $affectedRows 	= 0;

	public function __construct(array $params = array()) {
		if (count($params) > 0) {
   			$this->init($params);
  		}
	}
	
	/**
	 * 初始化方法
	 * @param array $params
	 * @return void
	 */
	public function init(array $params = array()) {
		if (count($params) > 0) {   
    		foreach ($params as $key => $val) {
      			$this->$key = $val;
      		}        
    	}		
	}
	
	/**
	 * 连接数据库
	 * @return void
	 */
	protected function connection() {
		$this->conn = mysql_connect($this->hostname, $this->username, $this->password);
		
		if (!$this->conn) {
			$this->error('Connect database be fail, Pealse inspect yours database setting!');
		}
		if ( !empty($this->charset) && version_compare(mysql_get_server_info(), '4.1.0', '>=') ) {
			mysql_query("SET NAMES '$this->charset'", $this->conn);
		}		
		
		if( ! mysql_select_db($this->database, $this->conn) ) {
			$this->error("Can't select name is '$this->dbname' database, Pealse inspect yours database setting");
		}
	}
	
	/**
	 * 执行sql语问
	 * @param string $sql
	 * @return void
	 */
	public function execute($sql) {
		$this->connection();
		$this->result = mysql_query($sql, $this->conn);
		if( mysql_error() ) {
			$this->error('MySQL Error:' . mysql_errno() . ':' . mysql_error());
		}
		
		if( preg_match("/^\\s*(select) /i", $sql) ) {
			$this->numRows = mysql_num_rows($this->result);
		}
		else if( preg_match("/^\\s*(insert|replace) /i", $sql) ) {
			$this->insertId = mysql_insert_id($this->conn);
		}
		else if( preg_match("/^\\s*(delete|update) /i",$sql) ) {
			$this->affectedRows = mysql_affected_rows($this->conn);
		}
			
	}

	/**
	 * 插入记录
	 * @param string $table 表名
	 * @param array $data 数据
	 * @return int 返回所插入记录的ID
	 */
	public function insert($table, array $data) {  
        $sql   = "INSERT INTO `{$table}`";
        $fields = $values = array();
        $field = $value = '';
        //遍历记录, 格式化字段名称与值
    	foreach($data as $key => $val) {
        	$fields[] = "`{$table}`.`{$key}`";
            $values[] = is_numeric($val) ? $val : ("'{$this->escape_string($val)}'");
        }
        $field = implode(',', $fields);
        $value = implode(',', $values);
        unset($fields, $values);
        $sql .= "({$field}) VALUES({$value})";
        
        $this->execute($sql);
        
        return $this->insertId;
	}
	
	/**
	 * 修改记录
	 * @param string $table 表名
	 * @param array $data 数据
	 * @param string $where 条件
	 * @return int 受影响的记录行数
	 */
	public function update($table, array $data, $where=NULL) {
        $sql = "UPDATE `{$table}` SET ";
		$values = array();
        foreach($data as $key => $val) {
        	$val      = is_numeric($val) ? $val : ("'{$this->escape_string($val)}'");
            $values[] = "`{$table}`.`{$key}` = {$val}";
        }
      	$value = implode(',', $values);
      	
      	$sql = $sql . $value;
      	if( !empty($where) ) {
      		$sql .= " WHERE {$where}";
      	}
        
        $this->execute($sql);
        
        return $this->affectedRows;
	}
	
	/**
	 * 删除记录
	 * @param string $table 表名
	 * @param string $where 条件
	 * @return int 返回受影响的行数
	 */
	public function delete($table, $where=NULL) {
		$sql = "DELETE FROM `{$table}`";
		if( !empty($where) ) {
			$sql .= " WHERE {$where}";
		}
		$this->execute($sql);
		return $this->affectedRows;
	}
	
	
	/**
	 * 获取查询结果
	 * @param string $sql
	 * @return array
	 */
	public function fetch($sql) {
		$this->execute($sql);		
		$i = 0;
		while($row = mysql_fetch_assoc($this->result)) {
			$data[$i] = (array)$row;
			$i++;
		}
		return $data;
	}
	
	/**
	 * 获取查询结果中的一行记录
	 * @param string $sql
	 * @return array
	 */
	public function fetchOne($sql) {
		$this->execute($sql);
		return mysql_fetch_assoc($this->result);
	}
	
	/**
	 * 获取查结果中的一个值
	 * @param int $x
	 * @param int $y
	 * @return miexd
	 */
	public function fetchField($sql, $x = 0, $y = 0) {
		$this->execute($sql);
		$i = 0;
		while( $row = mysql_fetch_row( $this->result ) ) {
			$data[$i] = $row;
			$i++;
		}
		return isset($data[$x][$y]) ? $data[$x][$y] : FALSE;
	}

	/**
	 * 获取执行插入语句后的id
	 * @return int
	 */
	public function insertId() {
		return $this->insertId;
	}
	
	/**
	 * 获取执行操作后，受影响的记录行数
	 * @return int
	 */
	public function affectedRows() {
		return $this->affectedRows;
	}
	
	/**
	 * 获取查询结果的总记录数
	 * @return int
	 */
	public function numRows() {
		return $this->numRows;
	}
	
	/**
	 * 释放查询结果集
	 * @return void
	 */
	public function freeResult() {
		if( isset($this->result) ) {
			mysql_free_result($this->result);
		}
	}
	
	/**
	 * 转义字符串的单引号（'）、双引号（"）、反斜线（\）与 NULL（NULL 字符）、回车字符
	 * @param $string
	 * @return $string
	 */
	public function escape_string($string) {
		$string = nl2br($string);
		return addslashes($string);
	}
	
	/**
	 * 关闭数据库连接
	 * @return void
	 */
	public function close() {
		if( isset( $this->conn ) ) {
			mysql_close($this->conn);
		}
	}
	
	public function error($msg) {
		$heading = 'An Database Error Was Encountered';
		
$error_tpl = <<<std
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="content-type" content="text/html;charset=utf-8" />
	<title>{$heading}</title>	
	<meta http-equiv="Cache-Control" content="no-cache" />
	<meta http-equiv="Pragma" content="no-cache" />
	<style type="text/css">
		* { margin:0; padding:0; }
		body { text-align:center; line-height:30px; }
		.error-box { border:1px solid #cccccc; margin:30px; padding:20px; text-align:left; }
		.error-box h3 { font-size:16px; color:#a00a00; font-weight:normal; }
		.error-box p { font-size:14px; color:#333333; }
	</style>	
</head>

<body>
	<div class="error-box">
		<h3>{$heading}</h3>
		<p>{$msg}</p>
	</div>
</body>
</html>
std;
		echo $error_tpl;
		exit;
	}
	
}

?>